#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2015年1月7日

@author: chenyongbing
'''
import sys,os
current_dir = os.path.dirname(__file__)
import datetime

import logging
sys.path.append(os.path.join(current_dir,'../../'))

from base.PyMySQL import ConnectMySQL
from base.localConfig import baseLocalConfig
import sys,datetime
zhimofi_host = baseLocalConfig.mysqlConfig['zhimofi']['host']
zhimofi_user = baseLocalConfig.mysqlConfig['zhimofi']['user']
zhimofi_passwd = baseLocalConfig.mysqlConfig['zhimofi']['passwd']
zhimofi_db = baseLocalConfig.mysqlConfig['zhimofi']['db']

myZhiMofi = ConnectMySQL(zhimofi_host,zhimofi_user,zhimofi_passwd,zhimofi_db)

class StatInMofi(object):
    '''
    classdocs
    '''


    def __init__(self):
        '''
        Constructor
        '''
        pass
    def get_slist_between_st_et(self,st,et):
        u'''获取2个时间间的所有日期'''
        dlist = []
        count = 0
        nday = (datetime.date(int(st[:4]),int(st[5:7]),int(st[8:]))+ datetime.timedelta(-1)).strftime('%Y-%m-%d')
        while nday < et:
            nday = (datetime.date(int(st[:4]),int(st[5:7]),int(st[8:]))+ datetime.timedelta(count)).strftime('%Y-%m-%d')
            count+=1
            dlist.append(nday)
        return dlist
    def stat_flow_by_hour(self,group_devices,day=None,hour=None):
        '''统计 flow'''
        slist = []
        if day!=None and hour!=None:
            st = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':00:00'
            et = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':59:59'
            slist.append([st,et])
        elif day!=None and hour==None:
            for hour in range(24):
                st = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':00:00'
                et = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':59:59'
                slist.append([st,et])
        elif day==None and hour!=None:
            day =datetime.datetime.today().strftime('%Y-%m-%d')
            st = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':00:00'
            et = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':59:59'
            slist.append([st,et])
        else:
            day =datetime.datetime.today().strftime('%Y-%m-%d')
            max_hour = int(datetime.datetime.today().strftime('%H'))
            for hour in range(max_hour):
                st = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':00:00'
                et = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':59:59'
                slist.append([st,et])
                
        for st,et in slist:
            hour = int(st[11:13])
            
            logging.info('Stat Flow By Hour Between %s and %s'%(st,et))
            
#         query = 'select g_d.group_id,d_u_d.deviceid ,date(d_u_d.start_time), sum(d_u_d.upflow+d_u_d.downflow) \
#             from device_user_detail d_u_d,group_device g_d ,devices d\
#             where d_u_d.start_time between "%s" and "%s" and d_u_d.deviceid = d.deviceid and d.id=g_d.device_id group by d_u_d.deviceid'%(st,et)
#         
            query = 'select deviceid,sum(upflow+downflow) from device_user_detail \
                where start_time between "%s" and "%s" group by deviceid'%(st,et)
            
            logging.info('Select Query:%s'%query)
            rets = myZhiMofi.SelectAll(query)
            
            if rets==None or len(rets)==0:
                logging.warning('No Flow Records Found From Tab %s.'%('device_user_detail'))
                rets = []
            else:
                logging.info('Found %s Flow Records From Tab %s.'%(len(rets),'device_user_detail'))
            flowdatas = {}
            for device ,flow in rets:
                flowdatas[device]=flow
            insert_datas = []
            for group,device,total_flow in group_devices:
                if flowdatas.has_key(device):
                    flow = flowdatas[device]
                else:
                    flow = 0
                
                insert_datas.append([ group,device,day,flow])
                #query = 'replace into device_flow_hour_stats set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s"'%(hour,flow,group,device,day)
                query = 'insert into device_flow_hour_stats set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s" \
                ON DUPLICATE KEY UPDATE hour_%s="%s"'%(hour,flow,group,device,day,hour,flow)
                logging.debug('Insert Stat Hour Flow Query:%s'%query)
                myZhiMofi.execute(query)
        

    def stat_flow_by_hour_realtime(self,group_devices,day=None,hour=None):
        '''统计 flow'''
        
        if day==None:
            day =(datetime.datetime.today()-datetime.timedelta(1.0/24)).strftime('%Y-%m-%d')
        if hour==None:
            hour = (datetime.datetime.today()-datetime.timedelta(1.0/24)).strftime('%H')
        
        query = 'select deviceid,total_flow from devices'
        
        logging.info('Query:%s'%query)
        rets = myZhiMofi.SelectAll(query)
        if rets==None or len(rets)==0:
            logging.warning('No Flow Records Found From Tab %s.'%('devices'))
            rets = []
        else:
            logging.info('Found %s Flow Records From Tab %s.'%(len(rets),'devices'))
        flowdatas = {}
        for device ,flow in rets:
            flowdatas[device]=flow
        
        bhours = []
        for h in range(int(hour)):
            bhours.append('hour_'+str(h))
        
       
        insert_datas = []
        for group,device,total_flow in group_devices:
            if flowdatas.has_key(device):
                flow = flowdatas[device]
            else:
                flow = 0
            
            insert_datas.append([ group,device,day,flow])
            #query = 'replace into device_flow_hour_stats set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s"'%(hour,flow,group,device,day)
            if len(bhours)==0:
                query = 'insert into device_flow_hour_stats set hour_%s=%s, group_id="%s" , deviceid="%s" , day="%s" \
            ON DUPLICATE KEY UPDATE hour_%s="%s"'%(hour,flow,group,device,day,hour,flow)
            else:
                query = 'insert into device_flow_hour_stats set hour_%s=%s-(%s), group_id="%s" , deviceid="%s" , day="%s" \
            ON DUPLICATE KEY UPDATE hour_%s=%s-(%s)'%(hour,flow,'+'.join(bhours),group,device,day,hour,flow,'+'.join(bhours))
            logging.debug('Insert Stat Hour Flow Query:%s'%query)
            #print query
            myZhiMofi.execute(query)
        


    def stat_flow_by_hour_history(self,group_devices,startTime=None,endTime=None):
        if startTime == None:
            startTime = datetime.datetime.today().strftime('%Y-%m-%d %H')
        if endTime == None:
            endTime = datetime.datetime.today().strftime('%Y-%m-%d %H')
        
        startDay = startTime[:10]
        startHour = int(startTime[11:])
        endDay = endTime[:10]
        endHour = int(endTime[11:])
        
        days  = self.get_slist_between_st_et(startDay, endDay)
        base_query = 'select deviceid, max(total_flow) - min(total_flow) from device_hb where   time like "%s %s%%" group by deviceid'
        for day in days:
            if day == startDay:
                hours = range(startHour,24)
            elif day == endDay:
                hours = range(endHour+1)
            else:
                hours = range(24)
            for hour in hours:
                query = base_query%(day,'0'*(2-len(str(hour)))+str(hour))
                logging.debug(query)
                
                rets = myZhiMofi.SelectAll(query)
                
                if rets==None or len(rets)==0:
                    logging.warning('No Flow Records Found From Tab %s On %s %s.'%('device_hb',day,hour))
                    rets = []
                else:
                    logging.info('Found %s Flow Records From Tab %s On %s %s.'%(len(rets),'device_hb',day,hour))
                flowdatas = {}
                for device ,flow in rets:
                    flowdatas[device]=flow
                insert_datas = []
                for group,device,total_flow in group_devices:
                    if flowdatas.has_key(device):
                        flow = flowdatas[device]
                    else:
                        flow = 0
                    
                    insert_datas.append([ hour,flow,group,device,day,hour,flow])
                    print group,device,day,hour,flow
                    #query = 'replace into device_flow_hour_stats set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s"'%(hour,flow,group,device,day)
                    query = 'insert into device_flow_hour_stats set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s" \
                    ON DUPLICATE KEY UPDATE hour_%s="%s"'%(hour,flow,group,device,day,hour,flow)
                    logging.debug('Insert Stat Hour Flow Query:%s'%query)
                    myZhiMofi.execute(query)
            

    
    def stat_auth_user_by_hour(self,group_devices,day=None,hour=None,auth_type=0):
        ''''''
        if auth_type==1:
            tab = 'device_authuser_hour_stats'
        else:
            tab = 'device_unauthuser_hour_stats'
        slist = []
        if day!=None and hour!=None:
            st = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':00:00'
            et = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':59:59'
            slist.append([st,et])
        elif day!=None and hour==None:
            for hour in range(24):
                st = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':00:00'
                et = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':59:59'
                slist.append([st,et])
        elif day==None and hour!=None:
            day =datetime.datetime.today().strftime('%Y-%m-%d')
            st = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':00:00'
            et = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':59:59'
            slist.append([st,et])
        else:
            day =datetime.datetime.today().strftime('%Y-%m-%d')
            max_hour = int(datetime.datetime.today().strftime('%H'))
            for hour in range(max_hour):
                st = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':00:00'
                et = day + ' '+'0'*(2-len(str(hour)))+str(hour)+':59:59'
                slist.append([st,et])
                
        for st,et in slist:
            logging.info('Stat (un)Auth By Hour Between %s and %s'%(st,et))
            hour = int(st[11:13])
            if auth_type==1:
                query = 'select deviceid,count(*) from device_user_detail \
                where start_time between "%s" and "%s" and auth_type="%s" group by deviceid'%(st,et,auth_type)  
            elif auth_type==0:
                 query = 'select deviceid,count(*) from device_user_detail \
                where start_time between "%s" and "%s" and auth_type in (0,2) group by deviceid'%(st,et)  
            logging.info('Query:%s'%query)
            rets = myZhiMofi.SelectAll(query)
            
            if rets==None or len(rets)==0:
                logging.warning('No (un)Auth Records Found From Tab %s.'%('device_user_detail'))
                rets = []
            else:
                logging.info('Found %s (un)Auth Records From Tab %s.'%(len(rets),'device_user_detail'))
            authdatas = {}
            for device ,count in rets:
                authdatas[device]=count
            insert_datas = []
            for group,device,total_flow in group_devices:
                if authdatas.has_key(device):
                    count = authdatas[device]
                else:
                    count = 0
                    
                insert_datas.append([ group,device,day,count])
                #query = 'replace into device_flow_hour_stats set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s"'%(hour,flow,group,device,day)
                query = 'insert into %s set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s" \
                ON DUPLICATE KEY UPDATE hour_%s="%s"'%(tab,hour,count,group,device,day,hour,count)
                logging.info('Insert Query:%s'%query)
                myZhiMofi.execute(query)
    

    def stat_auth_data_by_day(self,group_devices,day=None):
        if day==None:
            day = (datetime.datetime.today()-datetime.timedelta(1)).strftime('%Y-%m-%d')
        logging.info('Stat (un)Auth and Flow By Day %s'%(day))
        st = day + ' 00:00:00'
        et = day + ' 23:59:59'
        query = 'select deviceid,auth_type,count(*) from device_user_detail \
        where start_time between "%s" and "%s" group by deviceid,auth_type'%(st,et)
        logging.info('Select Query:%s'%query)
        rets = myZhiMofi.SelectAll(query)
        if rets==None:
            rets = []
        alldatas = {}
        for device, auth_type,count in rets:
            if not alldatas.has_key(device):
                alldatas[device] = {'auth':0,'unauth':0}
            if auth_type==1:
                auth_name = 'auth'
            elif auth_type in [0,2]:
                auth_name = 'unauth'
            else:
                auth_name = 'other'
            alldatas[device][auth_name] = count
        
        insert_datas = []
        for group,device,total_flow in group_devices:
            flow = total_flow
            if alldatas.has_key(device):
                #flow = alldatas[device]['flow']
                auth_count = alldatas[device]['auth']
                unauth_count = alldatas[device]['unauth']
                total_count = auth_count+unauth_count
            else:
                #flow = 0
                auth_count = 0
                unauth_count = 0
                total_count = auth_count+unauth_count
                
            #insert_datas.append([ group,device,day,count])
            #query = 'replace into device_flow_hour_stats set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s"'%(hour,flow,group,device,day)
            query = 'insert into device_day_rank_stats set  group_id="%s" , \
            deviceid="%s" , day="%s",auth_user_count="%s",nonauth_user_count="%s" ,total_user_count="%s"\
            ON DUPLICATE KEY UPDATE auth_user_count="%s",nonauth_user_count="%s" ,total_user_count="%s"'%(
                                                group,device,day,
                                                auth_count,unauth_count,total_count,
                                                auth_count,unauth_count,total_count)
            logging.info('Query:%s'%query)
            myZhiMofi.execute(query)
        #self.reload_total_flow()
    
    
    def stat_data_by_day(self,group_devices,day=None):
        if day==None:
            day = (datetime.datetime.today()-datetime.timedelta(1)).strftime('%Y-%m-%d')
        logging.info('Stat (un)Auth and Flow By Day %s'%(day))
        st = day + ' 00:00:00'
        et = day + ' 23:59:59'
        query = 'select deviceid,auth_type,count(*) from device_user_detail \
        where start_time between "%s" and "%s" group by deviceid,auth_type'%(st,et)
        logging.info('Select Query:%s'%query)
        rets = myZhiMofi.SelectAll(query)
        if rets==None:
            rets = []
        alldatas = {}
        for device, auth_type,count in rets:
            if not alldatas.has_key(device):
                alldatas[device] = {'auth':0,'unauth':0}
            if auth_type==1:
                auth_name = 'auth'
            elif auth_type in [0,2]:
                auth_name = 'unauth'
            else:
                auth_name = 'other'
            alldatas[device][auth_name] = count
        
        insert_datas = []
        for group,device,total_flow in group_devices:
            flow = total_flow
            if alldatas.has_key(device):
                #flow = alldatas[device]['flow']
                auth_count = alldatas[device]['auth']
                unauth_count = alldatas[device]['unauth']
                total_count = auth_count+unauth_count
            else:
                #flow = 0
                auth_count = 0
                unauth_count = 0
                total_count = auth_count+unauth_count
                
            #insert_datas.append([ group,device,day,count])
            #query = 'replace into device_flow_hour_stats set hour_%s="%s", group_id="%s" , deviceid="%s" , day="%s"'%(hour,flow,group,device,day)
            query = 'insert into device_day_rank_stats set flow="%s", group_id="%s" , \
            deviceid="%s" , day="%s",auth_user_count="%s",nonauth_user_count="%s" ,total_user_count="%s"\
            ON DUPLICATE KEY UPDATE auth_user_count="%s",nonauth_user_count="%s" ,total_user_count="%s"'%(
                                                flow,group,device,day,
                                                auth_count,unauth_count,total_count,
                                                auth_count,unauth_count,total_count)
            logging.info('Query:%s'%query)
            myZhiMofi.execute(query)
        self.reload_total_flow()
    
    def reload_total_flow(self):
        query = 'update devices set total_flow=0'
        myZhiMofi.execute(query)
    
    def get_group_device_and_flow(self):
        query = 'select gd.group_id,d.deviceid,d.total_flow from group_device gd,devices d where gd.device_id = d.id'
        datas = myZhiMofi.SelectAll(query)
        if datas==None or len(datas)==0:
            logging.error('Can Not Found Any group_device_flow From Tab :group_device,devices.')
            datas = []
        else:
            logging.info('Found %s group_device_flow From Tab:group_device,devices.'%(len(datas)))
        return datas
    
    def get_group_device(self):
        query = 'select gd.group_id,d.deviceid from group_device gd,devices d where gd.device_id = d.id'
        group_devices = myZhiMofi.SelectAll(query)
        if group_devices==None or len(group_devices)==0:
            logging.error('Can Not Found Any group_device From Tab :group_device,devices.')
            group_devices = []
        else:
            logging.info('Found %s group_device From Tab:group_device,devices.'%(len(group_devices)))
        return group_devices
        
if __name__=='__main__': 
    import argparse,re,datetime,time
    parser = argparse.ArgumentParser(description='args')
    parser.add_argument('--day',metavar=u"day",default=None)
    parser.add_argument('--hour',metavar=u"hour",default=None)
    parser.add_argument('--runType',metavar=u"run type:hour or day",default=None)
    parser.add_argument('--testing',metavar=u"testing",default=False)
    parser.add_argument('--startTime',metavar=u"day",default=None)
    parser.add_argument('--endTime',metavar=u"day",default=None)
    
    args = parser.parse_args()    
    
    day = args.day
    startTime = args.startTime
    endTime = args.endTime
    hour = args.hour
    runType = args.runType
    testing = args.testing
    
    runType = 'history'
    
    myStatInMofi = StatInMofi()
    group_devices = myStatInMofi.get_group_device_and_flow()
    if startTime!=None and endTime!=None:
        days = myStatInMofi.get_slist_between_st_et(startTime, endTime)
    else:
        days = []
#     myStatInMofi.stat_flow_by_hour_realtime(group_devices)
#     
#     sys.exit()
    if runType==None:
        now = datetime.datetime.today()
        minute = int(now.strftime('%M'))
        if minute<10:
            hour = int((now-datetime.timedelta(float(1)/24)).strftime('%H'))
            runType = 'day'
        else:
            hour = int((now).strftime('%H'))
            runType = 'hour'
        

        logging.info('Start Stat Data On Hour:%s.'%hour)
        if testing==False:
            #myStatInMofi.stat_flow_by_hour(group_devices,day=day,hour=hour)
            try:
                myStatInMofi.stat_flow_by_hour_realtime(group_devices,day=day,hour=hour)
                myStatInMofi.stat_auth_user_by_hour(group_devices, day=day, hour=hour, auth_type=0)
                myStatInMofi.stat_auth_user_by_hour(group_devices, day=day, hour=hour, auth_type=1)
            except:
                logging.error('Stat Hour:%s Faild.'%hour)
        if int(hour)==23 and runType=='day':
            logging.info('Start Stat Data On Day:%s.'%hour)
            if testing==False:
                try:
                    myStatInMofi.stat_data_by_day(group_devices, day=day)
                except:
                    logging.error('Stat Day:%s Faild.'%day)
    elif runType=='hour':
        #myStatInMofi.stat_flow_by_hour(group_devices,day=day,hour=hour)
        #myStatInMofi.stat_flow_by_hour_realtime(group_devices,day=day,hour=hour)
        for day in days:
            myStatInMofi.stat_auth_user_by_hour(group_devices, day=day, hour=hour, auth_type=0)
            myStatInMofi.stat_auth_user_by_hour(group_devices, day=day, hour=hour, auth_type=1)
            if hour==None:
                myStatInMofi.stat_auth_data_by_day(group_devices, day=day)
    elif runType=='day':
        myStatInMofi.stat_auth_data_by_day(group_devices, day=day)
        
    elif runType == 'history':
        startTime = '2015-04-29 0'
        endTime = '2015-04-30 0'
        myStatInMofi.stat_flow_by_hour_history(group_devices, startTime=startTime, endTime=endTime)
        